package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Property;
import com.xqgl.dao.PropertyDao;
import com.xqgl.util.ConnertionUtil;

public class PropertyDaoImp implements PropertyDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<Property> getAllUserInfos() {
		ArrayList<Property> propertys = new ArrayList<>();
		String sql = "select * from hpro";
		try {
			statement = conn.createStatement();
			ResultSet rst = statement.executeQuery(sql);
			while(rst.next()) {
				Property property = new Property();
				property.setId(rst.getInt("id"));
				property.setVillagename(rst.getString("villagename"));
				property.setBuildingname(rst.getString("buildingname"));
				property.setHpronum(rst.getString("hpronum"));
				property.setHproname(rst.getString("hproname"));
				property.setUsername(rst.getString("username"));
				property.setUserphone(rst.getInt("userphone"));
				property.setRoom(rst.getInt("room"));
				property.setBunit(rst.getInt("bunit"));
				property.setLayer(rst.getInt("layer"));
				property.setHprodesc(rst.getString("hprodesc"));
				property.setLivetime(rst.getString("livetime"));
				propertys.add(property);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return propertys;
	}
	
	@Override
	public int add(Property property) throws SQLException {
		String sql = "insert into hpro(villagename,buildingname,hpronum,hproname,username,userphone,room,bunit,layer,hprodesc,livetime)"
				+ "values(?,?,?,?,?,?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, property.getVillagename());
		ps.setString(2, property.getBuildingname());
		ps.setString(3, property.getHpronum());
		ps.setString(4, property.getHproname());
		ps.setString(5, property.getUsername());
		ps.setInt(6, property.getUserphone());
		ps.setInt(7, property.getRoom());
		ps.setInt(8, property.getBunit());
		ps.setInt(9, property.getLayer());
		ps.setString(10, property.getHprodesc());
		ps.setString(11, property.getLivetime());
		int a = ps.executeUpdate();
		ps.close();
		return a;
		
	}
	
	@Override
	public int update(Property property) throws SQLException {
		String sql = "update hpro set villagename = '"+property.getVillagename()+"', buildingname = '"+property.getBuildingname()+"', hproname ='"+property.getHproname()+"', username = '"+property.getUsername()+"', userphone = '"+property.getUserphone()+"', room = '"+property.getRoom()+"', bunit = '"+property.getBunit()+"', layer = '"+property.getLayer()+"', hprodesc = '"+property.getHprodesc()+"', livetime = '"+property.getLivetime()+"' where id = "+property.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}
	
	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from hpro where id = "+id+" ";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public Property findById(int id) throws SQLException {
		Property property = new Property();
		String sql = "select * from hpro where id = ?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		while(set.next()){
			property.setId(set.getInt("id"));
			property.setVillagename(set.getString("villagename"));
			property.setBuildingname(set.getString("buildingname"));
//			property.setHpronum(set.getString("hpronum"));
			property.setHproname(set.getString("hproname"));
			property.setUsername(set.getString("username"));
			property.setUserphone(set.getInt("userphone"));
			property.setRoom(set.getInt("room"));
			property.setBunit(set.getInt("bunit"));
			property.setLayer(set.getInt("layer"));
			property.setHprodesc(set.getString("hprodesc"));
			property.setLivetime(set.getString("livetime"));
		}
		ps.close();
		return property;
	}
}
